﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Import : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            Bind();
    }

    private void Bind()
    {
        
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string filePath = Server.MapPath("~/Controls/" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + Path.GetExtension(FileUpload1.FileName));
            FileUpload1.SaveAs(filePath);
            DataTable dt = ExcelToDataTable(filePath, "Sheet1");
            DY.BLL.tbl_Owner bllOwner = new DY.BLL.tbl_Owner();
            DY.Model.tbl_Owner modelOwner = new DY.Model.tbl_Owner();
            DY.BLL.tbl_Protocol bllProc = new DY.BLL.tbl_Protocol();
            DY.Model.tbl_Protocol modelProc = new DY.Model.tbl_Protocol();
            int count = 0;
            foreach (DataRow dr in dt.Rows)
            {
                count++;
                int OwnerId = 0;
                if (bllOwner.GetList("CardId='" + dr["身份证号"].ToString() + "'").Tables[0].Rows.Count > 0)
                {
                    OwnerId = (int)bllOwner.GetList("CardId='" + dr["身份证号"].ToString() + "'").Tables[0].Rows[0]["Id"];
                    modelOwner = bllOwner.GetModel(OwnerId);
                }
                modelOwner.CardId = dr["身份证号"].ToString();
                modelOwner.Mobile = dr["联系方式"].ToString();
                modelOwner.Name = dr["户主姓名"].ToString();
                modelOwner.Photo = "";
                modelOwner.Population = convertToInt(dr["安置人口"].ToString());
                modelOwner.VillageName = dr["所属行政村"].ToString();
                if (OwnerId > 0)
                    bllOwner.Update(modelOwner);
                else
                    OwnerId = bllOwner.Add(modelOwner);

                int procId = 0;
                if (bllProc.GetList("OwnerId=" + OwnerId).Tables[0].Rows.Count > 0)
                {
                    procId = (int)bllProc.GetList("OwnerId=" + OwnerId).Tables[0].Rows[0]["Id"];
                    modelProc = bllProc.GetModel(procId);
                }
                modelProc.An_Address = dr["安置区块"].ToString();
                modelProc.An_Area = convertToDecimal( dr["住房面积"].ToString());
                modelProc.An_BuildType = dr["建房方式"].ToString();
                modelProc.An_ChaiJianArea = convertToDecimal(dr["柴间面积"].ToString());
                modelProc.An_ChaiJianNo = dr["柴间编号"].ToString();
                modelProc.An_Covers = convertToDecimal(dr["占地面积"].ToString());
                modelProc.An_GeLou = convertToInt(dr["是否带阁楼"].ToString());
                modelProc.An_HouseNo = dr["房屋编号"].ToString();
                modelProc.An_HuXing = dr["户型"].ToString();
                modelProc.An_TaoShu = convertToInt(dr["套数"].ToString());
                modelProc.An_Time = convertToDateTime(dr["安置时间"].ToString());
                modelProc.An_Type = dr["安置方式"].ToString();

                modelProc.Chai_AttachedArea =convertToDecimal( dr["附房建筑面积"].ToString());
                modelProc.Chai_AttachedCovers =convertToDecimal( dr["附房占地面积"].ToString());
                modelProc.Chai_Fangqianwuhou =convertToDecimal( dr["房前屋后"].ToString());
                modelProc.Chai_MainArea =convertToDecimal( dr["正房建筑面积"].ToString());
                modelProc.Chai_MainCovers = convertToDecimal(dr["正房占地面积"].ToString());
                modelProc.Chai_ReclamationLotNo = dr["复垦地块编号"].ToString();
                modelProc.Chai_Structure = dr["结构"].ToString();
                modelProc.Chai_Time = convertToDateTime(dr["拆旧时间"].ToString());

                modelProc.Fei_AnZhiYanQiBuChang =convertToDecimal( dr["安置延期补偿"].ToString());
                modelProc.Fei_ChaiJiaJiangLi = convertToDecimal(dr["拆架奖励"].ToString());
                modelProc.Fei_ChaiJianJinE = convertToDecimal(dr["柴间金额"].ToString());

                modelProc.Fei_ChaiJiuFei = 0;

                modelProc.Fei_DaiJianShuiNiYingHua = convertToDecimal(dr["代建水泥硬化"].ToString());
                modelProc.Fei_DianBiaoAnZhuangFei = convertToDecimal(dr["电表安装费"].ToString());
                modelProc.Fei_DiJiBuChaFei = convertToDecimal(dr["地基补差费"].ToString());
                modelProc.Fei_FangKuanShuE = convertToDecimal(dr["房款数额"].ToString());
                modelProc.Fei_GeLouJinE = convertToDecimal(dr["阁楼金额"].ToString());
                modelProc.Fei_GuoDuQiFangZu = convertToDecimal(dr["过渡期房租"].ToString());
                modelProc.Fei_LaJiQingYunFei = convertToDecimal(dr["装潢垃圾清运费"].ToString());
                modelProc.Fei_ShiGongBaoZhengJin = convertToDecimal(dr["施工保证金"].ToString());
                modelProc.Fei_ShiGongShuiDianFei = convertToDecimal(dr["施工水电费"].ToString());

                modelProc.Fei_ShiJiaoFeiYong = 0;

                modelProc.Fei_ShuiBiaoKaiHuFei =convertToDecimal( dr["水表开户费"].ToString());
                modelProc.Fei_WeiJiaoShuE = convertToDecimal(dr["未交数额"].ToString());
                modelProc.Fei_WeiYueFaKuan = convertToDecimal(dr["违约罚款"].ToString());
                modelProc.Fei_YiJiaoShuE = convertToDecimal(dr["已缴数额"].ToString());
                modelProc.Fei_YingJiaoShuE = convertToDecimal(dr["应缴数额"].ToString());
                modelProc.Fei_ZhengCeXingBuZhu = convertToDecimal(dr["政策性补助"].ToString());
                modelProc.OwnerId = OwnerId;
                modelProc.ProtocolId = dr["协议编号"].ToString(); ;
                modelProc.ProtocolPhoto = "";
                modelProc.ProtocolType = dr["协议类型"].ToString();
                modelProc.Remark = dr["备注"].ToString();
                if (procId > 0)
                {
                    bllProc.Update(modelProc);
                }
                else
                {
                    bllProc.Add(modelProc);
                }
            }

            Label1.Text = "共导入信息" + count + "条";
        }
    }

    public decimal convertToDecimal(string c)
    {
        try
        {
            return decimal.Parse(c);
        }
        catch (Exception ex)
        {
            return 0;
        }
    }

    public int convertToInt(string c)
    {
        try
        {
            return int.Parse(c);
        }
        catch (Exception ex)
        {
            return 0;
        }
    }

    public DateTime convertToDateTime(string c)
    {
        try
        {
            return DateTime.Parse(c);
        }
        catch (Exception ex)
        {
            return DateTime.MinValue;
        }
    }

    /// <summary>  
    /// 读取Excel文件到DataSet中  
    /// </summary>  
    /// <param name="filePath">文件路径</param>  
    /// <returns></returns>  
    private DataTable ExcelToDataTable(string excelPath, string sheetName)
    {
        string strConnExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
       + excelPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
        OleDbConnection connExcel = new OleDbConnection(strConnExcel);
        OleDbDataAdapter daExcel = null;
        DataTable dtExcel = new DataTable();
        string strSqlExcel = string.Format("SELECT * FROM [{0}$]", sheetName);
        try
        {
            connExcel.Open();
            daExcel = new OleDbDataAdapter(strSqlExcel, connExcel);
            daExcel.Fill(dtExcel);
            return dtExcel;
        }
        catch (Exception ex)
        {
            //写错误日志 
            return dtExcel;
        }
        finally
        {
            connExcel.Close();
            daExcel.Dispose();
            connExcel.Dispose();
        }
    } 
}